﻿-------------------------------------------------------------------------------------------------------
-- spReturnInventory is called from the Returns application after the return InventoryTransaction records 
-- have been created.
-- This routine then clears and re-creats the ReturnDetail records to match with the inventory 
-- that was returned.
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[spReturnInventory]
	@OrderID int
AS
declare @foo table ( ProductID int not null, Qty int, Lot nvarchar(20), Location nvarchar(20) )

declare c1 cursor local for 
select T2.ProductID, T1.InventoryTransactionID
from tblInventoryTransaction T1
	join tblInventory T2 on T1.InventoryID = T2.InventoryID
where OrderID = @OrderID
	and InventoryTransactionTypeCode = 'S'
	and InventoryTransactionStatusCode = 'C'
open c1
declare @ProductID int
declare @InventoryTranscationID int
fetch next from c1 into @ProductID, @InventoryTranscationID
while @@FETCH_STATUS = 0
begin
	insert into @foo ( ProductID, Qty, Lot, Location )
	select T1.ProductID, T1.Qty * T2.Qty, T3.Lot, T3.Location
	from dbo.ProductBuildPlan(@ProductID) T1,
		tblInventoryTransaction T2 
		join tblInventory T3 on T2.InventoryID = T3.InventoryID
	where T2.InventoryTransactionID = @InventoryTranscationID
	
	fetch next from c1 into @ProductID, @InventoryTranscationID
end
close c1
deallocate c1

select * from @foo

RETURN 0